var express = require('express');
var router = express.Router();
// 导入MySQL模块
var mysql = require('mysql');
var pool = mysql.createPool(require('../conf/dbconf'));
var cateSQL = require('../conf/catesql');
//加密包
var crypto = require('crypto');

///////////////////用户模块///////////////////////////
//用户登录
router.post('/login',(req,res)=>{
  // 入参判断
  if(req.body.username==undefined) return res.json({msg:'param is empty'});
  if(req.body.password==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    connection.query('SELECT * FROM student WHERE login_name=? and login_pwd=?',[req.body.username,req.body.password],(err,resutls)=>{
      if (err) throw err;
      if(resutls.length>0){
        // 账户正常 写入session
        if(resutls[0].status==1){req.session.userid=resutls[0].login_name;res.cookie('username',resutls[0].login_name,{maxAge:1000*60*60*24*31, httpOnly: true }); res.json({status:resutls[0].status,uid:resutls[0].login_name});}
        // 账户禁用 写入session
        if(resutls[0].status==2){req.session.userInfo=resutls[0];res.json({status:resutls[0].status,uid:resutls[0].login_name});}
        // 账户待激活 写入session
        if(resutls[0].status==3){req.session.userInfo=resutls[0];res.json({status:resutls[0].status,uid:resutls[0].login_name});}
      }else res.json({status:404,msg:'user not exist'});
    });
    
  connection.release();
  });
});
// 用户注册
router.post('/reg',(req,res)=>{
  // 入参判断
  if(req.body.stu_name==undefined) return res.json({msg:'param is empty'});
  if(req.body.login_name==undefined) return res.json({msg:'param is empty'});
  if(req.body.login_pwd==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    // 判断login_name是否被占用
    connection.query('SELECT * FROM student WHERE login_name=?',req.body.login_name,(err,results)=>{
      if(err) throw err;
      // 没有占用
      if(results.length==0){
        let regsql='INSERT INTO student(stu_name,login_name,login_pwd) VALUES(?,?,?,?)';
        connection.query(regsql,[req.body.stu_name,req.body.login_name,req.body.login_pwd],(err,resutls)=>{
          if (err) throw err;
          if(resutls.affectedRows>0){
            res.json({msg:'reg success'});
          }else{
            res.json({msg:'reg fail'});
          }
        });
      }else{
        // 被注册
        res.json({msg:'user is exist'})
      }
    });
    connection.release();  
  });
});
/**
 * 用户修改密码
 * @method POST
 * @param {userid: "zhangsan", oldpwd: "123", newpwd: "456"}:Object
 * @returns 1.成功：{code:200,msg:'修改成功'}:Object 2.失败：{code:300,msg:'密码错误'}:Object
 */
router.post('/updatePwd',(req,res)=>{
  // 入参判断
  pool.getConnection((err,connection)=>{
    // 判断login_name是否被占用
    let updateSql='UPDATE student SET login_pwd=? WHERE login_name=? AND login_pwd=?'
    connection.query(updateSql,[req.body.newpwd,req.cookies.username,req.body.oldpwd],(err,results)=>{
      if(err) throw err;
      if(results.affectedRows>0) res.json({code:200,msg:'修改成功'});
      else res.json({code:300,msg:'密码错误'});
    });
    connection.release();
  });
});
//获取用户信息
router.get('/info',(req,res)=>{
  // 入参判断
  if(req.query.login_name==undefined) return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    connection.query('SELECT * FROM student WHERE login_name=?',req.query.login_name,(err,resutls)=>{
        if (err) throw err;
        if(resutls.length>0){
          res.json(resutls);
        }else res.json({msg:'user not exist'});
    });
    connection.release();
  }); 
});
/**
 * 用户QQ绑定
 * @method get
 * @param json 
 */
router.get('/bindqq',(req,res)=>{
  // 入参判断
  console.log(req.query);
  if(req.query.qqnum==undefined||req.query.qqnum=='') return res.json({msg:'param is empty'});
  //写入数据库
  pool.getConnection((err,connection)=>{
    // 判断login_name是否被占用
    connection.query('SELECT * FROM bind_lol WHERE qqnum=?',req.query.qqnum,(err,results)=>{
      if(err) throw err;
      // 没有占用
      if(results.length==0){
        let bindsql='INSERT INTO bind_lol(stu_id,qqnum,qqpwd) values(?,?,?)';
        connection.query(bindsql,[req.cookies.username,req.query.qqnum,req.query.qqpwd],(err,resutls)=>{
          if (err) throw err;
          if(resutls.affectedRows>0){
            res.json({code:200,msg:'bind success'});
          }else{
            res.json({code:400,msg:'bind by fail'});
          }
        });
      }else{
        // 被注册
        res.json({code:300,msg:'qqnum was bind'})
      }
    });
    connection.release();  
  });
});
/**
 * 查询QQ绑定
 * @method get
 * @param stu_id=3
 */
router.get('/findbindedqq',(req,res)=>{
// 数据库查找用户QQ信息
  pool.getConnection((err,connection)=>{
    if (err) res.json({code:404,msg:err})
    connection.query('SELECT qqnum  FROM bind_lol WHERE stu_id=?',req.cookies.username,(err,results)=>{
      if (err) res.json({code:404,msg:err})
      if(results.length>0)res.json({code:200,msg:results});
      else res.json({code:400,msg:'not find bind info'});
    });
    connection.release();
  });
});
/**
 * 绑定PUBG
 * @method get
 * @param json pubginfo:{"stu_id":"zhangsan","playerNames":"ctOSct"}
 */
// 导入puubgAPI接口
const Pubgapi = require('pubg-api');
let apiKey='eyJ0eXAiOiJKV1QiLCJhbGciOiJIUzI1NiJ9.eyJqdGkiOiIxOGYyMjU5MC02NzJlLTAxMzYtY2VjNy03YjNmNWYzNGQzMmEiLCJpc3MiOiJnYW1lbG9ja2VyIiwiaWF0IjoxNTMxMzA5NjQyLCJwdWIiOiJibHVlaG9sZSIsInRpdGxlIjoicHViZyIsImFwcCI6ImppYW94dWVwaW5nY2UifQ.KlO3wUZXWGgFsF4qS2GP9P1U5GWGAWhFLcXbABG5Qj8';
const apiInstance = new Pubgapi(apiKey);
router.get('/bindpubg',(req,res)=>{
  if(req.query.playerNames==undefined||req.query.playerNames=='') return res.json({msg:'param is empty'});
  // 入参判断 
  pool.getConnection((err,connection)=>{
    if(err) return res.json({code:400,msg:err});
    // 判断login_name是否被占用
    connection.query('SELECT * FROM bind_pubg WHERE playerNames=?',[req.query.playerNames],(err,results)=>{
      if(err) throw err;
      // 没有占用
      if(results.length==0){
        apiInstance.searchPlayers({playerNames:req.query.playerNames},'pc-as')
        .then(matches => {
          //写入数据库
        let bindsql='INSERT INTO bind_pubg(stu_id,playerNames,playerIds) values(?,?,?)';
        connection.query(bindsql,[req.cookies.username,req.query.playerNames,matches.data[0].id],(err,resutls)=>{
          if (err) throw err;
          if(resutls.affectedRows>0){
            res.json({code:200,msg:'bind success'});
            // 绑定后查找
            apiInstance.searchPlayers({playerNames:req.query.playerNames},'pc-as')
            .then(result=>{
              var ms=result.data[0];
              console.log(ms)
              let playerAccount=result.data[0].id;
              // 取得所有的比赛id
              var matchesArray=[];
              result.data[0].relationships.matches.data.forEach(element=>matchesArray.push(element.id));
              var mat=result.data[0].relationships;
              var matchesArrys=[];
              matchesArrys=matchesArray.slice(0,20);
              matchesArrys.forEach(element=>{
              apiInstance.loadMatchById(element,'pc-as').then(
                result=>{
                  var values=[];
                  values.push(element);
                  result.rosters.forEach(element => {
                  var wins=element.attributes.won;
                    element.participants.forEach((element,index) => {
                      if(element.attributes.stats.playerId==playerAccount){
                        console.log(element.attributes.stats);//////////////
                        var obj=element.attributes.stats;
                // 新建数组将是否吃鸡存入
                        
                        if(wins=="true"){values.push(1)}
                        else if(wins=="false"){ values.push(0);}    
               // 遍历对象属性并转成数组
                         var keys=[];
                         for(var key in obj){
                             if (obj.hasOwnProperty(key) === true){  
                                  keys.push(key);    
                               values.push(obj[key]);   
                               } 
                         }
                  //  存入数据库
                        pool.getConnection((err, connection) => {
                          connection.query('insert into pubg(match_id,wons,dbnos,assists,boosts,damage_dealt,death_type,headshot_kills,heals,killplace,killpoints,killpoints_delta,killstreaks,kills,lastkill_points,lastwin_points,longestkill,mostdamage,player_name,player_id,revives,ride_distance,roadKills,swim_distance,teamkills,timesurvived,vehicledestoy,walk_distance,weapons_acquired,winplace,winpoints,winpointsdelta) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', values, (err, resutls) => {
                            if(err) throw err;
                          });
                          connection.release();
                        });
                         
                      }
                    });
                  });
                },
                errors=>{console.log(errors);});
              });    
            });
            
          }else{
            res.json({code:400,msg:'bind by fail'});
          }
        });
      },err => {
        return res.json({code:404,msg:'user not found'});
      } );
      
    }
      else{
        // 被注册
        res.json({code:300,msg:'pubgAccount was bind'});
      }
    });
    connection.release(); 
    
  });
  
});
/**
 * 查询PUBG绑定
 * @method get
 * @param stu_id=3
 */
router.get('/findbindedpubg',(req,res)=>{
  pool.getConnection((err,connection)=>{
    if (err) res.json({code:404,msg:err})
    connection.query('SELECT playerNames FROM bind_pubg WHERE stu_id=?',req.cookies.username,(err,results)=>{
      if (err) res.json({code:404,msg:err})
      if(results.length>0)res.json({code:200,msg:results});
      else res.json({code:400,msg:'not find bind info'});
    });
    connection.release();
  });
});

// 查看pubg是否绑定
router.get('/getpubgdata',(req,res,next)=>{
  pool.getConnection((err, connection) => {
    connection.query('select playerNames from bind_pubg where stu_id=?',req.cookies.username,(err,result)=>{
      if(result.length>0){
        res.json({code:200})
      }
      else{
        res.json({code:400})
      }
    })
    connection.release();
  });
})
///////////////////////////////////绝地求生（蓝洞）模块//////////////////////

 // 查看pubg是否绑定
router.get('/searchpubg',(req,res)=>{
  pool.getConnection((err, connection) => {
      //  查找数据库
      connection.query('select playerNames from bind_pubg where stu_id=?',req.cookies.username,(err,result)=>{
        if(err) throw err;
        connection.query('select player_name, wons,damage_dealt,headshot_kills,ride_distance,timesurvived from pubg where player_name=? ORDER BY sorts LIMIT 50',result[0].playerNames,(err,result1)=>{
          if(err) throw err;
          var values=[0,0,0,0,0];
          var obb=[];
          console.log(result1.length);
				result1.forEach((element,index)=>{
            	values[0]+=parseInt(element.wons)/(result1.length);
            	values[1]+=parseInt(element.damage_dealt)/(result1.length);
            	values[2]+=parseInt(element.headshot_kills)/(result1.length);
            	values[3]+=parseInt(element.ride_distance)/(result1.length);
              values[4]+=parseInt(element.timesurvived)/(result1.length);
        })
             obb.push(parseInt(values[0]*100));
             obb.push(parseInt(values[1]/600*100));
             obb.push(parseInt(values[2]/2.5*100));
             obb.push(parseInt(values[3]/8000*100));
             obb.push(parseInt(values[4]/1920*100));
        console.log(obb);
        obb.unshift(result[0].playerNames)
        obb.forEach(function(value,index){
          connection.query('select game_adjust from pubg_percentage where game_skill=? And ?-game_levels>-1 ORDER BY game_levels DESC LIMIT 1',[index+1,obb[index]],(err,result2)=>{
            if(err) throw err;
           obb.push(result2);
            if(index==4){
              res.json(obb);
            }
          })
        })
        
        })
    // 继续查找
      apiInstance.searchPlayers({playerNames:result[0].playerNames},'pc-as')
            .then(result=>{
              var ms=result.data[0];
              console.log(ms)
              let playerAccount=result.data[0].id;
              // 取得所有的比赛id
              var matchesArray=[];
              result.data[0].relationships.matches.data.forEach(element=>matchesArray.push(element.id));
              var mat=result.data[0].relationships;
              var matchesArrys=[];
              matchesArrys=matchesArray.slice(0,20);
              matchesArrys.forEach(element=>{
              apiInstance.loadMatchById(element,'pc-as').then(
                result=>{
                  var values=[];
                  values.push(element);
                  result.rosters.forEach(element => {
                  var wins=element.attributes.won;
                    element.participants.forEach((element,index) => {
                      if(element.attributes.stats.playerId==playerAccount){
                        console.log(element.attributes.stats);//////////////
                        var obj=element.attributes.stats;
                // 新建数组将是否吃鸡存入
                        
                        if(wins=="true"){values.push(1)}
                        else if(wins=="false"){ values.push(0);}    
               // 遍历对象属性并转成数组
                         var keys=[];
                         for(var key in obj){
                             if (obj.hasOwnProperty(key) === true){  
                                  keys.push(key);    
                               values.push(obj[key]);   
                               } 
                         }
                  //  存入数据库
                        pool.getConnection((err, connection) => {
                          connection.query('insert into pubg(match_id,wons,dbnos,assists,boosts,damage_dealt,death_type,headshot_kills,heals,killplace,killpoints,killpoints_delta,killstreaks,kills,lastkill_points,lastwin_points,longestkill,mostdamage,player_name,player_id,revives,ride_distance,roadKills,swim_distance,teamkills,timesurvived,vehicledestoy,walk_distance,weapons_acquired,winplace,winpoints,winpointsdelta) values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)', values, (err, resutls) => {
                            if(err) throw err;
                          });
                          connection.release();
                        });
                         
                      }
                    });
                  });
                },
                errors=>{console.log(errors);});
              });    
            },err=> {
                console.log(err)
            });
    })
    connection.release();
  });
})
/**
 * 测试cookie
 */
router.get('/cookie',(req,res)=>{
  // req.cookies.username='lisi'
  // res.json({session:req.session.userid,cookie:req.cookies.username});
  res.json({session:req.session.userid,cookie:req.cookies.username});
});


/**
 * 8月14日改版后的数据提交
 */
router.get("/submitdata",function(req,res){
  console.log(req.query);
  //1.获取类型id，类型，量
    var cate_id = req.query.cate_id;
    var cate = req.query.cate;
    var spec = req.query.spec;
  //2.获取学生id
    var sid = req.cookies.username;
    console.log("登录用户是："+sid);
    console.log(cate_id+":"+cate+":"+spec);
    //组织数据
    var selectdata = [cate,sid];
    var updatedata = [spec,cate,sid];
    var insertdata = [spec,cate,sid,cate];
  //3.分辨提交类别
    var select;
    var insert;
    var update;
    if(cate_id==1){
      var select = cateSQL.selectDrink;
      var insert = cateSQL.insertDrink;
      var update = cateSQL.updateDrink;
    }else if(cate_id==2){
      var select = cateSQL.selectTrain;
      var insert = cateSQL.insertTrain;
      var update = cateSQL.updateTrain;
    }

  //4.写往数据库
    pool.getConnection((err,connection)=>{
      if(err) throw err;
      connection.query(select,selectdata,function(err,results){
        if(err) throw err;
        if(results.length==1){
          connection.query(update,updatedata,function(err,results){
            if(err) throw err;
            console.log("更新");
            res.json("1");
          });
        }else{
          connection.query(insert,insertdata,function(err,results){
            if(err) throw err;
            console.log("插入");
            res.json("1");
          });
        }
      });
      connection.release();
    });
    
});

/**
 * 饮料提示的词条(旧版本)
 */
router.get("/catedes",function(req,res){
  //分类id
  var cate_id = req.query.cate_id;
  console.log(cate_id);
  //用户id
  //stu_id = req.session.userid;
  stu_id = req.cookies.username;
  var select1;
  var select2=cateSQL.selectCateName;
  var select3;

  var sec_cate_id;
  var three_radio_desc;

  if(cate_id==1){
    select1=cateSQL.selectDrinkData;
    select3=cateSQL.selectDrinkDesc;
  }else if(cate_id==2){
    select1=cateSQL.selectTrainData;
    select3=cateSQL.selectTrainDesc;
  }
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    console.log("查询开始");
    connection.query(select1,stu_id,function(err,result){
      if(err) throw err;
      var len = result.length;
      //获取随机1,length的随机整数，应该前闭后开[0,length)。
      var a = Math.floor(Math.random()*len);
      //获取了分类id,和对应的数值
      if(cate_id==1){
       sec_cate_id = result[a].dr_cate_id;
      } else if(cate_id==2){
        sec_cate_id = result[a].tra_cate_id;
      }
      
      var sj_num = result[a].sj_num;
      var name;
      
      connection.query(select2,[sec_cate_id],function(err,results){
        console.log("======");
        console.log(select2);
        console.log(results);
        name = results[0].cate_name;
      });
      connection.query(select3,[sec_cate_id,sj_num],function(err,results){
        if(err) throw err;
        if(cate_id==1){
          if(results.length>0){
            res.json(name+"建议:"+results[0].drink_radio_desc);
          }else{
            res.json(name+"建议:饮用在合理范围。");
          }
        }else if(cate_id==2){
          if(results.length>0){
            res.json(name+"建议:"+results[0].sp_radio_desc);
          }else{
            res.json(name+"建议:饮用在合理范围。");
          }
        }
        
      });
    })
    connection.release();
  });
});

/**
 * 8月14日改版后查看建议
 */
router.get("/catedesc",function(req,res){
  //1. 获取分类id
    var cate_id = req.query.cate_id;
  //2. 获取用户id
    //var stu_id = req.session.userid;
    var stu_id = req.cookies.username;
    console.log("登录用户是:"+stu_id);
  //3. 查找分类名字
    var select1;
    var select2=cateSQL.selectCateName;
    var select3;
  //4. 获取二级分类id
    var sec_cate_id= req.query.sec_cate_id;
    var sec_cate_data;    //数据
    var three_radio_desc; //描述
  console.log("接受到的数据"+cate_id+":"+sec_cate_id);
  if(cate_id==1){
    select1=cateSQL.selectDrinkData;
    select3=cateSQL.selectDrinkDesc;
  }else if(cate_id==2){
    select1=cateSQL.selectTrainData;
    select3=cateSQL.selectTrainDesc;
  }
  
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    console.log("查询开始");
    //5. 获取某一个分类的值
    connection.query(select1,[stu_id,sec_cate_id],function(err,result){
      if(err) throw err;
      var len = result.length;
      //用户还未录入这个类别数据
      if(len==0){
        console.log("开始了");
        res.json({"data":"","desc":"建议：数据采集过少，请多提交数据。"});
        return ;
      }
      sec_cate_data =Math.floor(result[0].sj_num/7); 
      //6. 获取某一个分类的名字
      var name;
      connection.query(select2,[sec_cate_id],function(err,results){
        console.log(select2);
        console.log(results);
        name = results[0].cate_name;
      });
      //7. 获取某一个分类的建议词条
      connection.query(select3,[sec_cate_id,sec_cate_data],function(err,results){
        if(err) throw err;
        if(cate_id==1){
          if(results.length>0){
            //res.json(name+"建议:"+results[0].drink_radio_desc);
            res.json({"data":sec_cate_data,"desc":name+"建议:"+results[0].drink_radio_desc});
          }else{
            res.json({"data":sec_cate_data,"desc":name+"建议:数据采集过少，请多提交数据。"});
          }
        }else if(cate_id==2){
          if(results.length>0){
            //res.json(name+"建议:"+results[0].sp_radio_desc);
            res.json({"data":sec_cate_data,"desc":name+"建议:"+results[0].sp_radio_desc});
          }else{
            //res.json(name+"建议:锻炼在合理范围。");
            res.json({"data":sec_cate_data,"desc":name+"建议:数据采集过少，请多提交数据。"});
          }
        }
        
      });
    })
    connection.release();
  });

});
/**
 * 查询通知列表(未读已读标志在内,按时间排序)
 */
router.get("/notifylist",function(req,res){
  //1. 获取通知类别id，用户id
    var userid = req.cookies.username;
    var msg_type = req.query.msg_type;
  //2.sql语句和数据(降序查询)
    var sql = "select m.*,n.Status from Not_User_Table n left outer join Msg_Content_Table m "+
              " on m.Msg_Id=n.Msg_Id where m.Msg_Type=? and n.User_Id=? order by m.Time_Send desc"
    var sqlData = [msg_type,userid];
  //3.查询
  pool.getConnection((err,connection)=>{
    if(err) throw err;
    connection.query(sql,sqlData,function(err,result){
      var results = {"result":result,"msg_type":msg_type};

      res.locals.results=results;
      console.log(results);
      //res.json(results);
      res.render("notice-list.html");
      connection.release();
    });
  });
});

/**
 * 主页显示个人院系专业等信息
 */
router.get("/studentinfo",function(req,res){
  //1. 获取用户的id
    var stu_id = req.cookies.username;
  //2. sql查询(姓名,民族,性别，院系，专业)
    var sql = " select smt.*,zy.Cls_Name,yx.Col_Name,ls.Tea_Name from  "+
              " Stu_Massage_Table smt,Cls_Massage_Table zy,"+ 
              " Col_Massage_Table yx,Tea_Massage_Table ls"+
              " where smt.Stu_Id=? and smt.Cls_Id=zy.Cls_Id"+ 
              " and zy.Col_Id=yx.Col_Id  and zy.Tea_Id=ls.Tea_Id";
    console.log("我的id是"+stu_id);
  //3. 查询
    pool.getConnection((err,connection)=>{
      if(err) throw err;
      connection.query(sql,[stu_id],(err,results)=>{
        if(err) throw err;
        console.log(results);
        //写回浏览器
        res.json(results);
      });
  //4. 释放连接
    connection.release();
    })
});
//查询个人签到历史
router.get('/qr_history',function(req,res){
  //通过cookies获取用户id
  if(req.cookies.username==undefined||req.cookies.username=='') return res.json({msg:'param is empty'});

  pool.getConnection((err,connection)=>{
    connection.query('select * from sign_in where userid =?',req.cookies.username,(err,results)=>{
      if(err) throw err;
      res.json(results);
    });
    connection.release(); 
  });
});
/**
 * 班级课程表
 */
router.get("/kechengbiao",function(req,res){
  //1. 获取学生id
    var sid = req.cookies.username;
  //2. 通过id获取班级
    pool.getConnection((err,connection)=>{
      if(err) throw err;
      connection.query("select Cls_Id from Stu_Massage_Table where Stu_Id=?",[sid],(err,results)=>{
        if(err) throw err;
        console.log(results);
        var cls_id = results[0].Cls_Id;
        //3. 获取班级课程表
          var sql = "select bj.Cls_Name,km.Cou_Name,kcb.Wek_Day,kcb.Node_Day,ls.Tea_Name ,kcb.ClsRom_Id "+ 
                    "from Cls_Course_Table kcb,Cls_Massage_Table bj,Cou_Massage_Table km,Tea_Massage_Table ls "+
                    "where kcb.Cls_Id=bj.Cls_Id and kcb.Cou_Id=km.Cou_Id and kcb.Tea_Id=ls.Tea_Id and kcb.Cls_Id=?";
          connection.query(sql,[cls_id],(err,result)=>{
            if(err) throw err;
            console.log(result);
            //4. 写回数据
            res.json(result);
          });
        connection.release();
      });
    });
});


/**
 * 查询英雄联盟雷达图所需的数字以及拼接成功的提示
 */
router.get('/ldt',function(req,res){
  //1.获取session中的用户id
  if(req.cookies.username==undefined||req.cookies.username=='') return res.json({msg:'param is empty'});
  var sid = req.cookies.username;
  pool.getConnection((err,connection)=>{
    if(err) return err;
    var sj_arr = [];

  //2.根据sid获取qqnum，根据qqnum去python解析表(lol_radar)查找数据并加工，写入sj_lol
    connection.query("select * from lol_radar where qqnum in (select qqnum from bind_lol where stu_id=?)",[sid],(err,result)=>{
      if(err) throw err;
      console.log(result);
      //加工数据(输出，发育，团战，生存，防御)
      sch = result[0].magic*50+result[0].physic*50;
      fy = result[0].money*100;
      tz = result[0].assists*100;
      sc=result[0].existence*100;
      kda=result[0].defense*100;
      
      sj_arr[0]=sch;sj_arr[1]=fy;sj_arr[2]=tz;sj_arr[3]=sc;sj_arr[4]=kda;
  //3.查找五条数据的词条
  var desc = "";
  //遍历数组,根据值判断匹配的输出语句
  sj_arr.forEach(function(v,i,a){
    //如果值在1-30，就是第一档次，后面每5个分值为一个档次
    if(v-30<=0){
      var num = 1;
    }else{
      var num = Math.ceil((v-30)/5)+1;
    }
    //拼接查找的参数

    if(num<=9){
      var numb = '00'+(i+1)+'00'+num;
    }else{
      var numb = '00'+(i+1)+'0'+num;
    }
    
    //查找对应的文字说明
    connection.query('select dict_memo from base_dict where dict_item_code=?',[numb],(err,results)=>{
      if(i!=4){
        desc+=results[0].dict_memo+",";
      }else{
        desc+=results[0].dict_memo;
        res.json({'data':sj_arr,'desc':desc});
      }
      });
     });
    });
    connection.release();
  });
});

////////////////////////////////行走数据显示和行走建议//////////////////////////////////////////
/**
 * 从数据库获取行走数据
 * @method get
 * @returns json
 */
router.get('/getwalksdata',(req,res)=>{
  pool.getConnection((err,connection)=>{
    connection.query('SELECT * FROM `autodata` WHERE userid=? ORDER BY date DESC LIMIT 7',req.cookies.username,(err,results)=>{
      if(err) throw err;
      if(results.length>0) res.json(results);
      else res.json({msg:"还未提交数据！"});
    });
    connection.release();
  });
});
/**
 * 从数据库获取行走词条
 * @param  walk_sort
 */
router.get('/getwalkword',(req,res)=>{
  if(req.query.walk_sort==undefined||req.query.walk_sort=='') return res.json({msg:'param is empty'});
  pool.getConnection((err,connection)=>{
    connection.query('select wk_radio_desc from walk_ratio_healthy where ?-wk_ratio_num>0 order by wk_ratio_num desc limit 1',req.query.walk_sort,(err,results)=>{
      if(err) throw err;
      res.json(results);
    });
    connection.release();
  });
});

module.exports = router;

